﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Text;
using DBSchemaViewer.BackEnd.Entities;
using System.Linq;
using System.Xml.Linq;
using System.Windows; // Point class used as a param
using DBSchemaViewer.BackEnd.ModelXML.Helpers;

namespace DBSchemaViewer.BackEnd.ModelXML
{
    /// <summary>
    /// CRUD on the tables XML branch
    /// </summary>
    public class TableDAL
    {
        private DataStoreXML _dataStoreXML = DataStoreXML.Instance;


        /// <summary>
        /// Common for inserTable and UpdateTable
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        XElement GenerateTableNodeFromEntity(EntityTable table)
        {
            // 2. Create the Table XElement, including attributes: ID, posx, posy and name

            // Saving decimal values would be an issue, on regional settings in some places
            // we have commas in some other just dots, no sense to save that decimals
            // we are talking about absolute positions
            // same op implemented on method UpdateTableCanvasPosition
            XElement tableNode = new XElement("table", new XAttribute("id", table.InternalID),
                                                           new XAttribute("posx", Convert.ToInt32(table.CanvasCoordinates.X).ToString()),
                                                           new XAttribute("posy", Convert.ToInt32(table.CanvasCoordinates.Y).ToString()),
                                                        new XElement("name", table.Name)
                                                  );

            // 3. Assign the fields

            // 3.1 Create the fields piece of XML
            ObservableCollection<EntityTableFieldEntry> lstFields = table.Fields as ObservableCollection<EntityTableFieldEntry>;

            var fields = from p in table.Fields
                         select new XElement("field", new XAttribute("id", p.InternalID),
                                                            new XElement("name", p.FieldName),
                                                            new XElement("type", p.Type),
                                                            new XElement("length", p.Length),
                                                            new XElement("pk", p.IsPrimaryKey),
                                                            new XElement("fk", p.IsForeignKey)
                                                 );


            // 3.2 Hang it over the field root.
            XElement fieldroot = new XElement("fields", fields);

            // 3.3. Add the fields subnode to the table entry
            tableNode.Add(fieldroot);

            return tableNode;

        }


        public EntityTable CreateTable(EntityTable table)
        {
            // 1. Generate a unique identifier for this table and fields (in our case just a unique integer value)
            table.InternalID = _dataStoreXML.NextID;
            foreach (EntityTableFieldEntry currField in table.Fields)
            {
                currField.InternalID = _dataStoreXML.NextID;
            }


            XElement tableToInsert = GenerateTableNodeFromEntity(table);

            // 2. Create the Table XElement, including attributes: ID, posx, posy and name
            /*XElement tableToInsert = new XElement("table", new XAttribute("id", table.InternalID), 
                                                           new XAttribute("posx", table.CanvasCoordinates.X.ToString()), 
                                                           new XAttribute("posy", table.CanvasCoordinates.Y.ToString()),
                                                        new XElement("name", table.Name)
                                                  );
            
            // 3. Assign the fields

            // 3.1 Create the fields piece of XML
            ObservableCollection<EntityTableFieldEntry> lstFields = table.Fields as ObservableCollection<EntityTableFieldEntry>;

            var fields = from p in table.Fields
                         select new XElement("field", new XAttribute("id", p.InternalID),
                                                            new XElement("name", p.FieldName),
                                                            new XElement("type", p.Type),
                                                            new XElement("length", p.Length),
                                                            new XElement("pk", p.IsPrimaryKey),
                                                            new XElement("fk", p.IsForeignKey)
                                                 );


            // 3.2 Hang it over the field root.
            XElement fieldroot = new XElement("fields", fields);


            // 3.3. Add the fields subnode to the table entry
            tableToInsert.Add(fieldroot);
            */

            // 4. Append it under the <table> node
            XElement tableBranch = _dataStoreXML.TablesBranch;
            

            tableBranch.Add(tableToInsert);

            return table;
        }

        /// <summary>
        /// Table Update... some fields will be new (value == 0 or -1) just add a new internal id to those
        /// fields, we don't have to worry about relations (new fields does not have relations yet)
        /// </summary>
        /// <param name="?"></param>
        private void AssignInternalIDToNewFields(EntityTable table)
        {
            foreach (EntityTableFieldEntry currField in table.Fields)
            {
                // It's a new field? Yes, just assign a new internal ID for that field
                if (currField.InternalID < 1)
                {
                    currField.InternalID = _dataStoreXML.NextID;
                }
            }
        }

        /// <summary>
        /// 1. Create the XElement (similar to create table, but we are not generating id's).
        /// 2. Get the node
        /// 3. replace
        /// </summary>
        /// <param name="table"></param>
        public EntityTable UpdateTable(EntityTable entityTable)
        {
            // 1. Assign ID's to new fields
            // Here we could assing field Id's to -1
            // but beware to pass after this the entity to the UI Control
            AssignInternalIDToNewFields(entityTable);

            // 2. Generate the updated table subree
            XElement tableNode = GenerateTableNodeFromEntity(entityTable);

            // 3. Replace the old table subtree in the XML with the new one
            _dataStoreXML.UpdateTable(entityTable.InternalID, tableNode);

            // 4. return the updated entity
            return entityTable;
        }



        /// <summary>
        /// Just update a single table field in the XML model
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="field"></param>
        public void UpdateTableField(int tableID, EntityTableFieldEntry field)
        {
            XElement fieldNode = _dataStoreXML.SearchTableField(tableID, field.InternalID);

            if (fieldNode != null)
            {                
                fieldNode.Element("name").Value = field.FieldName;
                fieldNode.Element("fk").Value   = field.IsForeignKey.ToString();
                fieldNode.Element("pk").Value = field.IsPrimaryKey.ToString();
                fieldNode.Element("length").Value = field.Length.ToString();
                fieldNode.Element("type").Value = field.Type.ToString();
            }            
        }

        public void DeleteTable(int tableID)
        {
            _dataStoreXML.DeleteTable(tableID);
        }


        public EntityTable[] GetTables()
        {
            EntityTable[] tables = null;
            XElement[] tableNodes = null;

            // Get all child nodes from table XML root
            tableNodes = _dataStoreXML.ReturnAllTables();
            
            
            // Build up the table entities base on the XML content
            // On the X and Y coordinates convert them to INT (string way 
            // due to regional settings it could be a comma or a dot)
            // the save code has been modified in order to store always int
            // but for the northwind sample this could be an issue.
            tables = (from p in tableNodes
                      select new EntityTable()
                      {
                          InternalID = Convert.ToInt32(p.Attribute("id").Value)
                          ,Name = p.Element("name").Value
                          ,
                          Fields = CollectionHelper.ConvertFromListToObservableCollection((
                                      from pFields in p.Element("fields").Elements("field")
                                      select new EntityTableFieldEntry
                                      {
                                          InternalID = Convert.ToInt32(pFields.Attribute("id").Value)
                                          ,
                                          FieldName = pFields.Element("name").Value
                                          ,
                                          IsForeignKey = Convert.ToBoolean(pFields.Element("fk").Value)
                                          ,
                                          IsPrimaryKey = Convert.ToBoolean(pFields.Element("pk").Value)
                                          ,
                                          Length = Convert.ToInt32(pFields.Element("length").Value)
                                          ,
                                          Type = Convert.ToInt32(pFields.Element("type").Value)
                                      }
                                  ).ToList<EntityTableFieldEntry>())
                          ,CanvasCoordinates = new Point(
                                Convert.ToSingle(trimDecimalValues(p.Attribute("posx").Value)), 
                                Convert.ToSingle(trimDecimalValues(p.Attribute("posy").Value)))                       
                      }
                      ).ToArray();


            return tables;
        }


        /// <summary>
        /// Right now we are saving int values for the coordintas, but for the Northwind sample we 
        /// just have decimals, that's an issue taking into account that we are executing the app
        /// in the client side (regional settings), this means that sometime the separator is just a 
        /// comma, some others a dot.
        /// 
        /// Just to avoid any issue, we are going to trim all the dots or comma values (comma to the end)
        /// 
        /// This method is not quite nice, but it's effective (check later on if something more 
        /// elegant can be implemented)
        /// </summary>
        /// <param name="fullvalue"></param>
        /// <returns></returns>
        public string trimDecimalValues(string fullvalue)
        {
            string value = fullvalue;
            
            // Trim commas
            int commaIndex = value.IndexOf(',');
            if (commaIndex != -1)
            {
                value = value.Remove(commaIndex);
            }

            // Trim spaces
            commaIndex = value.IndexOf(',');
            if (commaIndex != -1)
            {
                value = value.Remove(commaIndex);
            }


            return value;
        }



        public EntityTable GetTable(int tableID)
        {         
            EntityTable tableToFind = null;
            XElement tableNode = null;

            // SearchTable in data store needs to be implemented
            tableNode = _dataStoreXML.SearchTable(tableID);
            if (tableNode != null)
            {
                tableToFind = fillUpEntityTableFromXMLTableNode(tableNode);

            }

            return tableToFind;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableNode"></param>
        /// <returns></returns>
        protected EntityTable fillUpEntityTableFromXMLTableNode(XElement tableNode)
        {
            EntityTable table = new EntityTable();

            if (tableNode != null)
            {
                table.InternalID  = Convert.ToInt32(tableNode.Attribute("id").Value);
                table.Name  = tableNode.Element("name").Value;
                table.CanvasCoordinates = new Point(Convert.ToSingle(tableNode.Attribute("posx").Value), Convert.ToSingle(tableNode.Attribute("posy").Value));                      
                table.Fields = CollectionHelper.ConvertFromListToObservableCollection(( 
                                       from pFields in tableNode.Element("fields").Elements("field")
                                       select new EntityTableFieldEntry
                                       {
                                           InternalID = Convert.ToInt32(pFields.Attribute("id").Value)
                                           ,FieldName = pFields.Element("name").Value
                                           ,IsForeignKey = Convert.ToBoolean(pFields.Element("fk").Value)
                                           ,IsPrimaryKey = Convert.ToBoolean(pFields.Element("pk").Value)
                                           ,Length       = Convert.ToInt32(pFields.Element("length").Value)
                                           ,Type         = Convert.ToInt32(pFields.Element("type").Value)
                                       }
                                   ).ToList<EntityTableFieldEntry>());
            }

            return table;
        }

        /// <summary>
        /// Sometimes we just need to update a single field on the entity,
        /// this method will serve as a common entry point, no need
        /// to repeats LINQ queires or loops everywhere to perform this.
        /// 
        /// On of the sample: update just the FK param on the entity.
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="fieldID"></param>
        /// <returns></returns>
        public EntityTableFieldEntry GetTableField(int tableID, int fieldID)
        {
            EntityTable table = GetTable(tableID);
            EntityTableFieldEntry field = null;

            if (table != null)
            {
                field = (from pField in table.Fields
                         where pField.InternalID == fieldID
                         select pField).First();
                        
                        
            }

            return field;
        }

        /// <summary>
        /// Lite method, when the user drags the table and changes the position
        /// we must reflect this change in the data model. Check for performance
        /// here (I think just only updating the coordinates is fast enough, if not
        /// check whether changin only on the LButtonUp event).
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="newPosition"></param>
        public void UpdateTableCanvasPosition(int tableID, Point newPosition)
        {
            XElement tableNode = null;

            tableNode = _dataStoreXML.SearchTable(tableID);

            if (tableNode != null)
            {
                // Saving decimal values would be an issue, on regional settings in some places
                // we have commas in some other just dots, no sense to save that decimals
                // we are talking about absolute positions
                // same op implemented on method GenerateTableNodeFromEntity
                tableNode.Attribute("posx").Value = Convert.ToInt32(newPosition.X).ToString();
                tableNode.Attribute("posy").Value = Convert.ToInt32(newPosition.Y).ToString();
            }                
        }

        /// <summary>
        /// Handy to use when creating relations and propagating FK is true
        /// to destination fields in destination table
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="fieldID"></param>
        /// <param name="isFK"></param>
        public void UpdateFKField(int tableID, int fieldID, bool isFK)
        {
            XElement fieldNode = _dataStoreXML.SearchTableField(tableID, fieldID);

            if (fieldNode != null)
            {
  
                fieldNode.Element("fk").Value = isFK.ToString();      
            }            

        }
    }
}
